use QuanLyDuAn
-- permission value
insert into Permission values (1,'Admin');
insert into Permission values (2,'User');
-- Admin value
insert into Admin values ('tamnv','123456',1);
insert into Admin values ('hungtm','123456',1);
insert into Admin values ('hieult','123456',1);
--StoreProcedure--
-------------ADMIN----------------------------
	--1. GetAdmin
	Create procedure GetAdmin
	as
	select * from Admin

	--2. ChangePass
	create proc changePass
		@username varchar(40),
		@password varchar(32)
	as
		update Admin
	set
		password = @password
	where
		username  = @username
	go
	--3. getAdminByUsername
	create procedure getAdminByUser
		@username varchar(40)
	as
	select * from Admin where username = @username

---------Interviewer--------------------------------
	--1. GetInterviewer
	Create procedure GetAllInterviewer
	as
	select * from Interviewer
	--2. InsertInterviewer
	Create procedure InsertInterviewer
		@iId varchar(10),
		@iUsername varchar(40),
		@iPassword varchar(32),
		@iFullname varchar(40),
		@iDepartment varchar(20),
		@pid int
	as
	insert into Interviewer values (@iId,@iUsername,@iPassword,@iFullname,@iDepartment,@pid)
	--3. update Interviewer
	Create procedure UpdateInterviewer
		@iId varchar(10),
		@iUsername varchar(40),
		@iPassword varchar(32),
		@iFullname varchar(40),
		@iDepartment varchar(20),
		@pid int
	as 
	update Interviewer set iUsername= @iUsername,iPassword=@iPassword,iFullname =@iFullname,iDepartment=@iDepartment,pid = @pid where iId = @iID;
	--4.delete Interviewer
	Create procedure DelInterviewer
		@iId varchar(10)
	as
	delete from  Interviewer where iId=@iId
	--5.search Interviewer
	Create procedure getInterviewr_Username
		@iUsername  varchar(40)
	as
	select * from Interviewer where iUsername like @iUsername

	--6. Get interviewer by username
	Create procedure getInterviewerByUser
		@username varchar(40)
	as
	select * from Interviewer where iUsername = @username
	--7. Change Password for Interviewer
	Create procedure chagePassInterviewer
		@username varchar(40),
		@iPassword varchar(32)
	as
	update Interviewer set iPassword = @iPassword where iUsername = @username

	--8. Get name
	Create proc getNameInterviewer
	as
	select iFullname from Interviewer
-------PERMISSION---------------------------------------------------------

	Create procedure GetPermission
	as
	select * from Permission
------- VACANCY----------------
	--1. Get All Vacancy
	drop proc getAllVacancy
	Create procedure getAllVacancy
	as
	select * from Vacancy
	--2. Insert values Vacancy
	Create procedure insertVacancy
		@vId varchar(10),
		@vDate datetime,
		@vStatus varchar(20),
		@vTitle varchar(20),
		@vDescription varchar(60),
		@vAmount int,
		@vDepartment varchar(20),
		@vDeadline datetime
	as
	insert into Vacancy values(@vID,@vDate,@vStatus,@vTitle,@vDescription,@vAmount,@vDepartment,@vDeadline)
	--3. Update Vacancy
	create procedure updateVacancy
		@vId varchar(10),
		@vDate datetime,
		@vStatus varchar(20),
		@vTitle varchar(20),
		@vDescription varchar(60),
		@vAmount int,
		@vDepartment varchar(20),
		@vDeadline datetime
	as 
	update Vacancy set vId=@vId,vDate=@vDate,vStatus=@vStatus,vTitle=@vTitle,vDescription=@vDescription,vAmount=@vAmount,vDepartment=@vDepartment,vDeadline=@vDeadline where vId=@vId
	--4. Delete Vacancy
	create procedure deleteVacancy
		@vId varchar(10)
	as delete from Vacancy where vId=@vId
	select * from Vacancy

--------APPLICANT----------------------------------
	--1.get all applicant getAllApplicant_period
	Create procedure getAllapplicant
	as
	select * from Applicant
	--them 

	Create procedure getAllApplicant_period
	@period varchar(20)
	as
	select * from Vacancy where vTitle= @period
update Applicant set aQualification ='Android' where aId='03'
	--2. insert Applicant
	create procedure insertApplicant
		@aId varchar(10),
		@aDate datetime,
		@aStatus varchar(20),
		@aName varchar(40),
		@aDateBirth datetime,
		@aPhone varchar(11),
		@aQualification varchar(60),
		@aExperience int
	as
	insert into Applicant values (@aId,@aDate,@aStatus,@aName,@aDateBirth,@aPhone,@aQualification,@aExperience)
	--3 . Update Applicant
	Create procedure updateApplicant
		@aId varchar(10),
		@aDate datetime,
		@aStatus varchar(20),
		@aName varchar(40),
		@aDateBirth datetime,
		@aPhone varchar(11),
		@aQualification varchar(60),
		@aExperience int
	as
	update Applicant set aDate =@aDate,aStatus = @aStatus,aName = @aName,aDateBirth = @aDateBirth,aPhone = @aPhone,aQualification =@aQualification,aExperience = @aExperience
	where aID =@aID
	--4 .Delete Applicant
	Create procedure DelApplicant
		@aId varchar(10)
	as
	delete from  Applicant where aId = @aId
	--5 . Load Name 
	Create proc getNameApplicant
	as
	select aName from Applicant
------INTERVIEW_PLAN-----------------------------------------------------
			--1. Get Interview _Plan
			--drop--
			Drop procedure getInterview_Plan
			--Create again
			Create procedure getInterview_Plan
			as
			select a.iFullname ,b.aName, c.IpDate, c.IpTime, c.IpStatus
			from Interviewer a,Applicant b, Interview_Plan c
			where a.iId = c.iId and b.aId =c.aId
			--Drop--
			Drop procedure insertInterviewPlan
			--Create again---
			--2. Insert InterviewPlan
			Create procedure insertInterviewPlan
				@iId varchar(10),
				@aId varchar(10),
				@IpDate datetime,
				@IpTime varchar(20),
				@IpStatus varchar(20) 
			as
			Insert into Interview_Plan values(@iId,@aId,@IpDate,@IpTime,@IpStatus) 
			--Drop--
			Drop procedure updateInterviewPlan
			--Create again---
			--3.Update Interview plan
			Create procedure updateInterviewPlan
				@iId varchar(10),
				@aId varchar(10),
				@IpDate datetime,
				@IpTime varchar(20),
				@IpStatus varchar(20) 
				as
			update Interview_Plan set aId =@aId,IpDate=@IpDate,IpStatus =@IpStatus, IpTime = @IpTime  where iId= @iId
			--4.Delete Interview plan
			--drop --
			Drop procedure DelInterviewPlan
			--Create---
			Create procedure DelInterviewPlan
				@aId varchar(10)
			as
			delete from  Interview_Plan where aId = @aId

			------trigger---------------
			--delete interviewer 	
				create trigger trgdelete_Interviewer
				on Interviewer instead of delete
				as
				--xoa bang
				delete from Interview_Plan where iId in (select iId from Deleted);
				delete from Interviewer where iId in (select iId from Deleted);
				delete from Interview_Result where iId in (select iId  from Deleted);
			--delete applicant
				create trigger trgdelete_Applicant
				on Applicant instead of delete
				as
			--xoa bang
				delete from Applicant where aId in (select aId from Deleted);	
				delete from Interview_Plan where aId in (select aId from Deleted);
				delete from Interview_Result where aId in (select aId from Deleted);
-------INTERVIEW_RESULT-------------------------------------------------------------------------
	--1. get interviewResult
	Create procedure getInterview_Result
	as
	select a.iFullname ,b.aName, c.IrResult, c.IrNote
	from Interviewer a,Applicant b, Interview_Result c
	where a.iId = c.iId and b.aId =c.aId
	--2. insert interviewResul
	Create procedure insertInterviewResult
		@iId varchar(10),
		@aId varchar(10),
		@IrResult varchar(60),
		@IrNote varchar(100)
	as
	Insert into Interview_Result values(@iId,@aId,@IrResult,@IrNote) 
	--3. delte interviewResult
	Create proc deleteInterviewResult
		@aId varchar(10)
	as
	Delete from Interview_Result where aId = @aId
	--4 .Update InterviewResult
	Create procedure updateInterviewResult
	@iId varchar(10),
	@aId varchar(10),
	@IrResult varchar(60),
	@IrNote varchar(100)
	as
	update Interview_Result set iId =@iId,IrResult=@IrResult,IrNote =@IrNote  where aId= @aId
	--5 .Search InterviewResult

	Create proc searchResultByIrResult
	@IrResult varchar(60)
	as
	select a.iFullname ,b.aName, c.IrResult, c.IrNote
	from Interviewer a,Applicant b, Interview_Result c
	where a.iId = c.iId and b.aId =c.aId and c.IrResult = @IrResult
	--6. Search InterviewResultByApplicantName
	
	Create proc searchResultByApplicant
	@aId varchar(60)
	as
	select a.iFullname ,b.aName, c.IrResult, c.IrNote
	from Interviewer a,Applicant b, Interview_Result c
	where a.iId = c.iId and b.aId =c.aId and c.aId = @aId
-----------Search -------------------------------------------------------------------------------
	--VACANCY
	--1. Search status
	Create proc searchVacancyBySatus
		@vStatus varchar(20)
	as
	select * from Vacancy where vStatus = @vStatus
	--2. Search title
	Create proc searchVacancyByTitle
		@vTitle varchar(20)
	as
	select * from Vacancy where vTitle = @vTitle
	--3. Search by date
	Create proc searchVacancyByDate
		@vDeadline datetime
	as
	select * from Vacancy where vDeadline = @vDeadline
	--4. Load Title 
	Create proc getVacancyTitle
	as
	select vTitle from Vacancy
	--5. Search by Department
	Create proc searchVacancyByDepartment
		@vDepartment varchar(20)
	as
	select * from Vacancy where vDepartment = @vDepartment
	-- INTERVIEWER
	--1.Search by name
	Create procedure searchInterviewByName
		@iFullname varchar(40)
	as
	select * from Interviewer where iFullname = @iFullname
	--2.Search by Department
	Create proc searchInterviewByDepartment
		@iDepartment varchar(20)
	as
	select * from Interviewer where iDepartment = @iDepartment
	--APPLICANT
	--1. search by Name
	Create proc searchApplicantName
		@aName varchar(40)
	as
	select * from Applicant where aName = @aName

	--2.search by date
	Create proc searchApplicantByDate
		@aDate datetime
	as
	select * from Applicant where aDate = @aDate
	--3.search by status
	Create proc searchApplicantByStatus
		@aStatus varchar(20)
	as
	select * from Applicant where aStatus = @aStatus
	--IV--INTERVIEW_PLAN
	Create proc searchPlanInterviewer
		@iId varchar(10)
	as
	select a.iFullname ,b.aName, c.IpDate, c.IpTime, c.IpStatus
			from Interviewer a,Applicant b, Interview_Plan c
			where a.iId = c.iId and b.aId =c.aId and c.iId = @iId
	
	
	-- search interviewPlan by date

	Create proc searchInterviewPlanByDate
			@IpDate datetime
		as
	select a.iFullname ,b.aName, c.IpDate, c.IpTime, c.IpStatus
				from Interviewer a,Applicant b, Interview_Plan c
				where a.iId = c.iId and b.aId =c.aId and c.IpDate = @IpDate
	Create proc searchInterviewPlanByDate
			@IpDate datetime
		as
	select a.iFullname ,b.aName, c.IpDate, c.IpTime, c.IpStatus
				from Interviewer a,Applicant b, Interview_Plan c
				where a.iId = c.iId and b.aId =c.aId and c.IpDate = @IpDate
	-- search interviewPlan by Status
	Create procedure searchInterviewPlanByStatus
		@IpStatus varchar(20)
	as
	select a.iFullname ,b.aName, c.IpDate, c.IpTime, c.IpStatus
			from Interviewer a,Applicant b, Interview_Plan c
			where a.iId = c.iId and b.aId =c.aId and c.IpStatus = @IpStatus
	--Statistic Vacancy
	Drop proc statisticsVacancy
	Create proc statisticsVacancy
	@vDate1 datetime,
	@vDate2 datetime
	as
	select * from Vacancy
	where vDate between @vDate1 and @vDate2
	--Statistic Applicant
	Create proc statisticsApplicant
	@aDate1 datetime,
	@aDate2 datetime
	as
	select * from Applicant
	where aDate between @aDate1 and @aDate2
	-- Statistics InterviewPlan
	Create proc statisticsInterviewPlan
	@aDate1 datetime,
	@aDate2 datetime
	as
	select * from Interview_Plan
	where IpDate between @aDate1 and @aDate2
	--Check_Value
	--Vacancy
	Create proc CheckVacancy
	@vId varchar(10)
	as
	select *  from Vacancy where vId = @vId

	select * from Interviewer

	--Interviewer
	Create proc CheckInterviewer
	@iId varchar(10)
	as
	select *  from Interviewer where iId = @iId
	--Applicant
	Create proc CheckApplicant
	@aId varchar(10)
	as
	select *  from Applicant where aId = @aId

	select * from Applicant
	--Load Applicant interview plan
	Create proc LoadApplicantOpen
	as
	select * from Applicant where aStatus = 'Open'
	--Check status Applicant
	Create proc CheckStatusApplicant
	@aID varchar(10)
	as
	select aStatus from Applicant where aID = @aID
	
